package com.bosch.in.rtp1.ept.dao.impl;

import java.math.BigDecimal;
import java.sql.Array;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Struct;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import com.bosch.in.rtp1.ept.constant.CommonConstants;
import com.bosch.in.rtp1.ept.constant.FinishRecipeConstant;
import com.bosch.in.rtp1.ept.dao.FinishRecipeDao;
import com.bosch.in.rtp1.ept.model.dto.FshRcpChartFactors;
import com.bosch.in.rtp1.ept.model.dto.FshRcpChartValues;
import com.bosch.in.rtp1.ept.model.dto.FshRcpRecord;
import com.bosch.in.rtp1.ept.model.dto.SelectingString;
import com.bosch.in.rtp1.ept.model.technical.Condition4FinishRecipe;
import com.bosch.in.rtp1.ept.util.ConverterUtility;
import com.bosch.in.rtp1.ept.util.SQLStatementLoader;

/**
 * Concrete JDBC implementation of FinishRecipeDao. Provide actions for searching on Finish Recipes table.
 * @author LIV1HC
 *
 */
public class FinishRecipeJDBCDao extends GenericDao implements FinishRecipeDao{

	/**
	 * Constructor to inject connection
	 * @param con	DB connection
	 */
	
	public FinishRecipeJDBCDao(Connection connection) {
		super(connection);
	}

	@Override
	public List<SelectingString> getAllLocation() throws SQLException {
		
		// retrieve SQL statement for loading location list
		String queryString = SQLStatementLoader.getFshRcpLocationList();
		
		// excute query
		ResultSet resultSet = executeQuery(queryString,null);
		
		// examine result 
		List<SelectingString> locations = new ArrayList<SelectingString>();
		while (resultSet.next()) {
			SelectingString location = new SelectingString();
			location.setValue(resultSet.getString(FinishRecipeConstant.LOCATION_DB));
			locations.add(location);
		}
		
		// close statement, result set
		resultSet.close();
		close();
		return locations;
	}

	@Override
	public List<FshRcpChartValues> getFactorList(
			Condition4FinishRecipe condition4FinishRecipe) throws SQLException {
		
		// retrieve SQL statement for loading FshRcpChartValues list
		String queryString = SQLStatementLoader.getFactor4Chart();

		// excute query
		ResultSet resultSet = executeQuery(queryString,condition4FinishRecipe.getParameterValues().toArray());
		
		// examine result 
		List<FshRcpChartValues> chartValues = new ArrayList<FshRcpChartValues>();
		while (resultSet.next())
		{
			FshRcpChartValues cValue = new FshRcpChartValues();
			cValue.setPriority(resultSet.getInt(FinishRecipeConstant.CHART_PRIO_DB));
			cValue.setProdArea(resultSet.getInt(FinishRecipeConstant.CHART_PROD_AREA_DB));
			List<FshRcpChartFactors> exFactors = new ArrayList<FshRcpChartFactors>();
			Array array = resultSet.getArray(FinishRecipeConstant.CHART_FACTOR_LIST_DB);			
            Object[] factorList = (Object[]) array.getArray();  
            for (int i = 0; i < factorList.length; i++) {  
               Struct factor = (Struct) factorList[i];  
               Object[] attrib = factor.getAttributes();
               FshRcpChartFactors exFactor = new FshRcpChartFactors();
               exFactor.setxPlan	((ConverterUtility.bigDecimalToFloat((BigDecimal) attrib[FinishRecipeConstant.X_PLAN_INDEX_DB])));
               exFactor.setxRW		((ConverterUtility.bigDecimalToFloat((BigDecimal) attrib[FinishRecipeConstant.X_RW_INDEX_DB])));
               exFactor.setxRWH		((ConverterUtility.bigDecimalToFloat((BigDecimal) attrib[FinishRecipeConstant.X_RWH_INDEX_DB])));
               exFactor.setFxPlan	((ConverterUtility.bigDecimalToFloat((BigDecimal) attrib[FinishRecipeConstant.FX_PLAN_INDEX_DB])));
               exFactor.setFxRW		((ConverterUtility.bigDecimalToFloat((BigDecimal) attrib[FinishRecipeConstant.FX_RW_INDEX_DB])));
               exFactor.setFxRWH	((ConverterUtility.bigDecimalToFloat((BigDecimal) attrib[FinishRecipeConstant.FX_RWH_INDEX_DB])));
               exFactor.setYear		((((BigDecimal) attrib[FinishRecipeConstant.YEAR_INDEX_DB]).intValue()));
               exFactor.setMonth	((((BigDecimal) attrib[FinishRecipeConstant.MONTH_INDEX_DB]).intValue()));
               exFactor.setCount	(((BigDecimal) 	attrib[FinishRecipeConstant.COUNT_INDEX_DB]).intValue());
               exFactors.add(exFactor);
            }  
            cValue.setFshRcpChartFactors(exFactors);
            chartValues.add(cValue);
		}
		
		// close statement, result set
		resultSet.close();
		close();
		return chartValues; 
	}
	
	@Override
	public List<SelectingString> getAllStage() throws SQLException {
		
		// retrieve SQL statement for loading Stage list
		String queryString = SQLStatementLoader.getFshRcpStageList();
		
		//execute query
		ResultSet resultSet = executeQuery(queryString,null);
		
		//examine result
		List<SelectingString> stages = new ArrayList<SelectingString>();
		while (resultSet.next()) {
			SelectingString stage = new SelectingString();
			stage.setValue(resultSet.getString(FinishRecipeConstant.STAGE_DB));
			stages.add(stage);
		}
		
		// close statement, result set
		resultSet.close();
		close();
		return stages;
	}

	@Override
	public List<SelectingString> getAllEqpType() throws SQLException {
		// retrieve SQL statement for loading EqpType list
		String queryString = SQLStatementLoader.getFshRcpEqpTypeList();		
		
		// excute query
		ResultSet resultSet = executeQuery(queryString,null);
		
		//examine result
		List<SelectingString> eqptypes = new ArrayList<SelectingString>();
		while (resultSet.next()) {
			SelectingString eqptype = new SelectingString();
			eqptype.setValue(resultSet.getString(FinishRecipeConstant.EQPTYPE_DB));
			if (eqptype.getValue().equalsIgnoreCase(FinishRecipeConstant.EMPTY_EQP_TYPE_VALUE))
			{
				eqptype.setValue(FinishRecipeConstant.EMPTY_EQP_TYPE_STRING);
			}
			eqptypes.add(eqptype);
		}
		
		// close statement, result set
		resultSet.close();
		close();
		return eqptypes;
	}

	@Override
	public List<FshRcpRecord> getFinshedRecipes(
			Condition4FinishRecipe condition4FinishRecipe) throws SQLException {
		String queryString = SQLStatementLoader.getFshRcpRecordQuery();
		List<FshRcpRecord> records = new ArrayList<FshRcpRecord>();
		ResultSet resultSet = executeQuery(queryString,condition4FinishRecipe.getParameterValues().toArray());
		DecimalFormat df = new DecimalFormat(CommonConstants.NUMBER_FORMAT);
		while (resultSet.next()) {
				FshRcpRecord rcpRecord = new FshRcpRecord();
				rcpRecord.setLotId(resultSet.getString(FinishRecipeConstant.FACTOR_LOT_ID));
				rcpRecord.setPartId(resultSet.getString(FinishRecipeConstant.FACTOR_PART_ID));
				rcpRecord.setFactorValue(Float.valueOf(df.format(resultSet.getFloat(FinishRecipeConstant.FACTOR_VALUE))));
				rcpRecord.setProdArea(resultSet.getString(FinishRecipeConstant.FACTOR_PROD_AREA));
				rcpRecord.setLocation(resultSet.getString(FinishRecipeConstant.FACTOR_LOCATION));
				rcpRecord.setStage(resultSet.getString(FinishRecipeConstant.FACTOR_STAGE));
				rcpRecord.setEqpType(resultSet.getString(FinishRecipeConstant.FACTOR_EQPTYPE));
				rcpRecord.setRecipe(resultSet.getString(FinishRecipeConstant.FACTOR_RECIPE));
				if (condition4FinishRecipe.getFactorType()>0)
				{
					rcpRecord.setTimeRev(resultSet.getTimestamp(FinishRecipeConstant.FACTOR_TIME_REV));
				}
				if (condition4FinishRecipe.getFactorType()==2)
				{
					rcpRecord.setHoldCode(resultSet.getString(FinishRecipeConstant.FACTOR_HOLD_CODE));
					rcpRecord.setHoldComment(resultSet.getString(FinishRecipeConstant.FACTOR_HOLD_COMT));
					rcpRecord.setHoldDuration(Float.valueOf(df.format(resultSet.getFloat(FinishRecipeConstant.FACTOR_HOLD_DUR))));
					rcpRecord.setReleaseComment(resultSet.getString(FinishRecipeConstant.FACTOR_RELEASE_COMT));
					rcpRecord.setCustomerName(resultSet.getString(FinishRecipeConstant.FACTOR_CUSTOMER_NAME));
				}
				records.add(rcpRecord);
			}
		
		// close statement, result set
		resultSet.close();
		close();
		return records;
	}

	@Override
	public int getTotalFRRecords() throws SQLException {
		// load query statement to get number of all finished recipe records
		String queryString = SQLStatementLoader.getFshRcpCountingQuery();
		ResultSet resultSet = executeQuery(queryString, null);
		int totalNumber = 0;
		if (resultSet.next()) {
			totalNumber = resultSet.getInt(1);
		}
		// close statement, result set
		resultSet.close();
		return totalNumber;
	}
}
